package com.css.bjdt_core.cell.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.css.bjdt_core.cell.dao.CellDao;
import com.css.bjdt_core.cell.entity.Cell;
import com.css.bjdt_core.cell.entity.Cell.CellType;
import com.css.bjdt_core.cell.entity.CellExample;
import com.css.bjdt_core.executive.report.ReportCache;
import com.css.bjdt_core.util.CommonUtil;
import com.css.bjdt_core.util.entity.FanChaResult;

@Service
public class CellService {
	@Autowired
	private Dao dao;
	@Autowired
	private CellDao cellDao;

	private ReportCache cache = ReportCache.getInstance();

	public List<Cell> getRawCellsByTemplateId(String templateId) {
		if (StringUtils.isBlank(templateId)) {
			return null;
		}
		//return dao.query(Cell.class, Cnd.where("TEMPLATE_ID", "=", templateId));
		CellExample example = new CellExample();
		example.createCriteria().andTemplateIdEqualTo(templateId);
		return cellDao.selectByExample(example);
	}

	public List<Cell> getCellStrategyByTemplateId(String templateId) {
		List<Cell> cellList = (List<Cell>) cache.getCell(templateId);
		if (cellList == null) {
			cellList = cellDao.selectCellStrategyByTemplateId(templateId);
			cache.putCell(templateId, cellList);
		}
		return cellList;
	}

	public List<Cell> getRawCellsByTemplateIdAndType(String templateId, CellType cellType) {
		return dao.query(Cell.class,
				Cnd.where("TEMPLATE_ID", "=", templateId).and("CELL_TYPE", "=", cellType.getKey()));
	}

	public List<Cell> getCellsByTemplateIdAndType(String templateId, CellType cellType) {
		if (StringUtils.isBlank(templateId) || cellType == null) {
			return null;
		}
		//		return dao.query(Cell.class, Cnd.where("TEMPLATE_ID", "=", templateId).and("CELL_TYPE", "=", cellType.getKey()));
		List<Cell> res = new ArrayList<>();
		for (Cell cell : getCellStrategyByTemplateId(templateId)) {
			if (cellType.getKey().equals(cell.getCellType())) {
				res.add(cell);
			}
		}
		return res;
	}

	public boolean batchInsert(List<Cell> records) {
		if (records == null || records.size() == 0) {
			return false;
		}
		for (Cell cell : records) {
			if (StringUtils.isBlank(cell.getCellId())) {
				cell.setCellId(CommonUtil.getUUID());
			}
			cellDao.insert(cell);
		}
		//		dao.insert(records);
		return true;
	}

	public boolean clear(String templateId) {
		if (StringUtils.isBlank(templateId)) {
			return false;
		}
		//		dao.clear(Cell.class, Cnd.where("TEMPLATE_ID", "=", templateId));
		cellDao.deleteByTemplateId(templateId);
		return true;
	}

	//查询出所有需要填写批注的单元格
	public List<Cell> getAllNeedCommentCell(String templateId) {
		/*		List<Cell> cellList = dao.query(Cell.class, Cnd.where("TEMPLATE_ID", "=", templateId).and("IS_COMMENT", "=", "2"));
				return cellList;*/
		List<Cell> res = new ArrayList<>();
		for (Cell cell : getCellStrategyByTemplateId(templateId)) {
			if ("2".equals(cell.getIsComment())) {
				res.add(cell);
			}
		}
		return res;
	}

	//根据CELLID查询出当前单元格的对象
	public Cell getACellEntity(String cellId) {
		/*		Cell cell = dao.fetch(Cell.class, Cnd.where("CELL_ID", "=", cellId));
				return cell;*/
		return cellDao.selectByPrimaryKey(cellId);
	}

	public void update(List<Cell> cells) {
		//		dao.updateIgnoreNull(cells);
		for (Cell cell : cells) {
			cellDao.updateByPrimaryKeySelective(cell);
		}
	}

	public String getCellIdByTemplateIdRowCol(String templateId, String row, String col) {
		if (row == null || col == null) {
			return null;
		}
		Integer rowI, colI;
		try {
			rowI = Integer.valueOf(row);
			colI = Integer.valueOf(col);
		} catch (Exception e) {
			return null;
		}
		for (Cell cell : getCellStrategyByTemplateId(templateId)) {
			if (rowI.equals(cell.getAbsRowNum()) && colI.equals(cell.getAbsColNum())) {
				return cell.getCellId();
			}
		}
		return null;
	}

	public Cell getRawCellByTemplateIdRowCol(String templateId, String row, String col) {
		return dao.fetch(Cell.class,
				Cnd.where("templateId", "=", templateId).and("ABS_ROW_NUM", "=", row).and("ABS_COL_NUM", "=", col));
	}

	public Cell getCellByTemplateIdRowCol(String templateId, String row, String col) {
		if (row == null || col == null) {
			return null;
		}
		Integer rowI, colI;
		try {
			rowI = Integer.valueOf(row);
			colI = Integer.valueOf(col);
		} catch (Exception e) {
			return null;
		}
		for (Cell cell : getCellStrategyByTemplateId(templateId)) {
			if (rowI.equals(cell.getAbsRowNum()) && colI.equals(cell.getAbsColNum())) {
				return cell;
			}
		}
		return null;
	}

	//通过相对坐标和报表的ID获得当前这个CELL实体
	public Cell getCellByTemplateIdRelRowCol(String templateId, String relRow, String relCol) {
		if (relRow == null || relCol == null) {
			return null;
		}
		Integer rowI, colI;
		try {
			rowI = Integer.valueOf(relRow);
			colI = Integer.valueOf(relCol);
		} catch (Exception e) {
			return null;
		}
		for (Cell cell : getCellStrategyByTemplateId(templateId)) {
			if (rowI.equals(cell.getRelRowNum()) && colI.equals(cell.getRelColNum())) {
				return cell;
			}
		}
		return null;
	}

	public void updateByID(Cell cell) {
		try {
			cellDao.updateByPrimaryKey(cell);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void updateCellSqlCommentById(String cellId, String sqlComment) {
		Sql strSql = Sqls
				.create("update RPT_CELL set SQL_CELL_COMMENT = '" + sqlComment + "' where CELL_ID = '" + cellId + "'");
		dao.execute(strSql);
	}

	public FanChaResult getResult(String Sql, final String id, String dzzId) {
		Sql sql = Sqls.create(Sql);
		sql.params().set("dzzId", dzzId);

		//根据SQL先查出来该张报表的所有审核信息
		sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				FanChaResult result = new FanChaResult();
				List<String> list = new ArrayList<String>();
				while (rs.next()) {
					list.add(rs.getString(id));
				}
				result.setPrimaryIdList(list);
				return result;
			}
		});
		dao.execute(sql);
		//return (Result) sql.getEntity();
		return sql.getObject(FanChaResult.class);
	}

	public List<Cell> selectCellByTemplateId(String templateId) {
		return getCellStrategyByTemplateId(templateId);
	}

}
